﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace QLSV
{
    class SinhVienDAO
    {
        private static OleDbConnection TaoKetNoi()
        {
            string strcnn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=QLSV.mdb";
            OleDbConnection cnn = new OleDbConnection(strcnn);
            cnn.Open();
            return cnn;
        }
        public static void DAO_ThemSV(SinhVienDTO sv)
        {
            OleDbConnection cnn = TaoKetNoi();
            string sql_sv = "insert into SinhVien(MSSV,HoTen,NgaySinh,DiaChi)" + "values (@mssv,@ht,@ns,@dc)";
            OleDbCommand cmd = new OleDbCommand(sql_sv, cnn);
            cmd.Parameters.AddWithValue("@mssv", sv.MSSV);
            cmd.Parameters.AddWithValue("@ht", sv.HoTen);
            cmd.Parameters.AddWithValue("@ns", sv.NgaySinh.ToShortDateString());
            cmd.Parameters.AddWithValue("@dc", sv.DiaChi);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static OleDbDataReader DAO_viewSV(int mSSV)
        {
            OleDbConnection cnn = TaoKetNoi();
            string sql = "select HoTen,NgaySinh,DiaChi from SinhVien where MSSV =  @mS";
            OleDbCommand cmd = new OleDbCommand(sql, cnn);
            cmd.Parameters.AddWithValue("@mS", mSSV);
            OleDbDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rd;
        }
        public static DataTable DAO_viewAllSV()
        {
            OleDbConnection cnn = TaoKetNoi();
            string sql = "select MSSV, HoTen,NgaySinh,DiaChi from SinhVien";
            OleDbCommand cmd = new OleDbCommand(sql, cnn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static void UpdateSV(SinhVienDTO sv)
        {
            OleDbConnection cnn = TaoKetNoi();

            string sql = "update SinhVien set HoTen=@ht,NgaySinh=@ns,DiaChi=@dc where MSSV=@ms";
            OleDbCommand cmd = new OleDbCommand(sql, cnn);

            
            cmd.Parameters.AddWithValue("@ht", sv.HoTen);
            cmd.Parameters.AddWithValue("@ns", sv.NgaySinh);
            cmd.Parameters.AddWithValue("@dc",sv.DiaChi);
            cmd.Parameters.AddWithValue("@ms", sv.MSSV);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }

    }
}
